from numpy import dtype, median
from numpy.core.shape_base import stack
import pandas as pd
from pandas.core.indexes.base import Index


# # ————————创建excel
# df = pd.DataFrame({"id": [1, 2, 3], "name": ['zs', 'ls', 'ww']})  # 添加{‘表名’：[‘数据’，‘数据’]}
# df = df.set_index('id')    #设置索引
# df.to_excel('c:/123/sss.xlsx')
# print(df)

# # ——————————读取文件
# people=pd.read_excel('c:/123/People - 副本.xlsx',header=1)  #header =(n) 从第n+1行提前标题 前面是空行的不用写
# print(people.shape)  #.shape  多少行多少列
# print(people.columns) #.columns  显示列名
# print(people.head(6)) #。head（）显示头部多少行
# print(people.tail(2)) # .tail () 显示尾部多少行
# people=pd.read_excel('c:/123/People - 副本1.xlsx',header=None,index_col='id')  #没有列名 制定索引是id
# people.columns=['id','type','title','fstname','midname','lastname']  #添加列名
# people = people.set_index('id')  #设置索引 people。set_index('id',inplace=true) 直接改
# people.to_excel('c:/123/out.xlsx')

# # ———————————行 列 单元格 （序列（类似字典）)
# # d={'a':1000,"b":2000}
# # s1 = pd.Series(d)
# # print(s1.index)
# # l1 =[100,200,300]
# # l2=['x','y','z']
# # s2 =pd.Series(l1,index=l2)
# # print(s1,s2)

# s1=pd.Series([1,2,3],index=[1,2,3],name='A')
# s2=pd.Series([10,20,30],index=[1,2,3],name='B')
# s3=pd.Series([100,200,300],index=[1,2,3],name='C')

# # df=pd.DataFrame({s1.name:s1,s2.name:s2,s3.name:s3})  #列显示
# df =pd.DataFrame([s1,s2,s3])    #行显示
# print(df)

# # ————————————添加数据 修改数据
# from datetime import date, timedelta

# def add_month(d,md):
#     yd =md//12
#     m=d.month+md%12
#     if m!=12:
#         yd+=m//12
#         m=m%12
#     return date(d.year+yd,m,d.day)   #基础月算法

# books = pd.read_excel('C:/Users/gpf/Desktop/py_basic_study/pandas/Books (1).xlsx', usecols='c:f', skiprows=3, index_col=None,
#                       dtype={'ID': str, 'InStore': str,'Date':str})  # kiprows 跳过空行  usecols=选取的列
# # print(type(books['ID']))
# # books['ID'].at[0]=100  #设置值 ID[0]=100
# start1 = date(2020,4,3)
# for i in books.index:
#     books.at[i,'ID']= i+1  # 使用循环设置设置ID的值   datefarme 改法 （二维表  行/列）
#     books['InStore'].at[i] = 'yes' if i % 2 == 0 else 'no'  # 设置InStore的yes no  先取Series一维表 再取值
#     books['Date'].at[i] = add_month(start1,i)  #这里有小算法
# books.set_index('ID',inplace=True)
# books.to_excel('C:/Users/gpf/Desktop/py_basic_study/pandas/outBooks (1).xlsx')

#  ——————————————计算填充
# books=pd.read_excel('C:/Users/gpf/Desktop/py_basic_study/pandas/Books.xlsx',index_col='ID')
# # books['Price']=books['ListPrice']*books['Discount']  #操作符的重载 * 会重载  直接列相乘
# # for i in range(8,12):  #一般用在局部需要计算的问题
# #     # books['Price'].at[i]=books['ListPrice'].at[i]*books['Discount'].at[i]   #转为series后再乘法
# #     books.at[i,'Price']=books.at[i,'ListPrice']*books.at[i,'Discount']        #datafarme 二维表乘法  

# def add_2(x):
#     return x+2
# books['ListPrice']=books['ListPrice'].apply(add_2) #不是调用 不写（）
# print(books)

# —————————————排序
# products = pd.read_excel(r'C:\Users\gpf\Desktop\py_basic_study\pandas\007\List.xlsx',index_col='ID')
# products.sort_values(by=['Worthy','Price'],inplace=True,ascending=[True,False])  #inplace 本地修改 ascending=False 向上排序 否 多个使用列表
# print(products)

# —————————————筛选
students = pd.read_excel(r'C:\Users\gpf\Desktop\py_basic_study\pandas\007\Students.xlsx',index_col='ID')

def age_18_to_30(a):
    return 30>a>=18
def level_a(s):
    return 85<=s<=100

#loc 定位
students=students.loc[students['Age'].apply(age_18_to_30)].loc[students['Score'].apply(level_a)]

print(students)